USE AdventureWorks2008

--Using For XML to generate XML results

SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.AccountNumber
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO

--Using OpenXML to query XML

DECLARE @xml_text VARCHAR(4000), @i INT
SELECT @xml_text = ' <root>
<person LastName="White" FirstName="Johnson" Title="Mr."/>
<person LastName="Green" FirstName="Marjorie" Title="Mrs."/>
<person LastName="Carson" FirstName="Cheryl" Title="Miss"/></root>'

EXEC sp_xml_preparedocument @i OUTPUT, @xml_text

SELECT * FROM  OPENXML(@i, '/root/person') WITH 
		(LastName nvarchar(50),
		FirstName nvarchar(50),
		Title nvarchar(8))

EXEC sp_xml_removedocument @i

--Using XQuery to query XML

SELECT JobCandidateID, 
Resume.value('declare namespace ns=
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
concat((/ns:Resume/ns:Name/ns:Name.First)[1], " ",
(/ns:Resume/ns:Name/ns:Name.Last)[1])',
'nvarchar(60)') AS FullName,
Resume.query('declare namespace ns=
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
for $ed in /ns:Resume/ns:Education
where $ed/ns:Edu.Level="Bachelor" and $ed/ns:Edu.Major="Business"
return $ed') AS Education
FROM HumanResources.JobCandidate



